import pandas as pd
from sqlalchemy import create_engine

# Read the Excel file
prediction = pd.read_excel('prediction.xlsx')

# Create a connection to your MySQL database
engine = create_engine('mysql+pymysql://red:2003721%40chen@106.15.46.141:3306/red')
# 如果某些 seller_no 不是以 'seller_' 开头，就加上前缀
prediction['seller_no'] = prediction['seller_no'].astype(str)
prediction['seller_no'] = prediction['seller_no'].apply(lambda x: f'seller_{x}' if not x.startswith('seller_') else x)

# 同时格式化其他字段的数据类型，确保合并时数据一致

prediction['product_no'] = prediction['product_no'].astype(str)
prediction['product_no'] = prediction['product_no'].apply(lambda x: f'product_{x}' if not x.startswith('product_') else x)

prediction['warehouse_no'] = prediction['warehouse_no'].astype(str)
prediction['warehouse_no'] = prediction['warehouse_no'].apply(lambda x: f'wh_{x}' if not x.startswith('wh_') else x)
# Insert the data into the MySQL table
prediction.to_sql('prediction', con=engine, if_exists='append', index=False)
# Query the data from the MySQL table
query_df = pd.read_sql('SELECT * FROM prediction', con=engine)

# Print the queried data
print(query_df)